<?php 
namespace App\Http\Controllers\TongGaoApi;

use Illuminate\Http\Request;
use App\Http\Controllers\Controller;
use App\Models\Mysql\tonggao\article;
use App\Models\Mysql\tonggao\term;
use App\Models\Mysql\tonggao\term_detail;
use App\Models\Mysql\tonggao\sentence;
use Symfony\Component\DomCrawler\Crawler;
use App\libraries\diff\Diff;	

use Excel;

class TermController extends Controller
{
//	use GlobalData;

	const ROUTE_PREFIX = '/api/*';

	protected $request;

	protected $curTime;

	protected $debug;


	/**
	 * 创建新的`DictController`控制器实例
	 * @param \Illuminate\Http\Request $request
	 */
	public function __construct(Request $request)
	{
		$this->request = $request;
		$this->error = [];
		$this->curTime = time();

		if(config('app.debug')) {
			$this->debug = true;
			error_reporting(E_ALL | E_STRICT);
		} else {
			$this->debug = false;
		}
	}

	public function check456()
    {
		set_time_limit(0);
		$outputFileName = iconv('UTF-8','GB2312',"F:\\000_booktran\\book7&8_term\\七八册术语整合_in456.txt");		
		$sentences = sentence::select("select content from sentence where article_id between 4 and 28");
		$terms = term::select("select id,chinese_key from jlsterm_78");

		foreach($terms as $term){
			$curTerm = $term->chinese_key;
			echo "curTerm=$curTerm</br>";flush();ob_flush();
			$in456=false;
			foreach($sentences as $sentence){
				if (mb_strpos($sentence->content,$curTerm)!==false)
					$in456=true;
			}	
			term::select(sprintf("update jlsterm_78 SET in456=%d where id=%d",($in456)?1:0,$term->id));
			file_put_contents($outputFileName,sprintf("%d|$curTerm|%s\r\n",$term->id,($in456)?'Y':'N'),8);
		}
	}

	public function in456term()
    {
		set_time_limit(0);
		$outputFileName = iconv('UTF-8','GB2312',"F:\\000_booktran\\book7&8_term\\七八册术语整合_in456term.txt");		
		$terms = term::select("select id,chinese_key,in456 from jlsterm_78 order by id");
		foreach($terms as $term){
			$jlsterm_78_id = $term->id;
			$curTerm = $term->chinese_key;
			if($term->in456==1){
				$in456term = false;
				$temp_r = term::select("select id from term where chinese_key=\"{$term->chinese_key}\"");
				if (!empty($temp_r)){
					$term_id = $temp_r[0]->id;
					$temp_r = term_detail::select("select id from term_detail where article_id between 4 and 28 and term_id=$term_id  and type<100");
					if (!empty($temp_r)){
						$in456term = true;
					}
				}
				term::select(sprintf("update jlsterm_78 SET in456term=%d where id=%d",($in456term)?1:0,$jlsterm_78_id));
				file_put_contents($outputFileName,sprintf("%d|$curTerm|%s\r\n",$jlsterm_78_id,($in456term)?'Y':'N'),8);	
			}
			else{
				file_put_contents($outputFileName,sprintf("%d|$curTerm|\r\n",$jlsterm_78_id),8);	
			}
		}
	}
	
    public function markCommon()
    {
		set_time_limit(0);
		//$fileName = iconv('UTF-8','GB2312',"F:\\000_booktran\\数据准备\\文集4-佛法-lishu-v3.csv");
		//$fileName = iconv('UTF-8','GB2312',"F:\\000_booktran\\数据准备\\output\\因缘&境界-杨雪赵悦-20180306-王一晨-李舒.csv");
		$option=2;
		switch($option){
			case 1:
				$inputFileName = iconv('UTF-8','GB2312',"F:\\000_booktran\\数据准备\\output\\术语提炼-第4-6册（汇总）.csv");
				$outputFileName = iconv('UTF-8','GB2312',"F:\\000_booktran\\数据准备\\output\\术语提炼-第4-6册（汇总）common.txt");
				break;
				
			case 2:
				$inputFileName = iconv('UTF-8','GB2312',"F:\\000_booktran\\数据准备\\术语提炼-第4~6册-句子序号-forImport.csv");
				$outputFileName = iconv('UTF-8','GB2312',"F:\\000_booktran\\数据准备\\术语提炼-第4~6册-句子序号common.txt");
				break;
			
		}
		
		file_put_contents($outputFileName,'');
		$file = file_get_contents("$inputFileName");
		$lines = explode("\r\n",$file);
		$terms = $term_types = $term_comments = array();
		$lastSentenceOccurCnt = 0;
		foreach($lines as $k=>$line){
			$fields = explode(",",$line);
			for($i=0;$i<5;$i++)
				$fields[$i] = empty($fields[$i]) ? "" : trim($fields[$i]);
				
			if (empty($fields)){
				if ($k<($count($lines)-1)){
					echo "error: line $k  为空</br>"; 
				}else{
					echo "最后一行 line $k  为空</br>"; 
				}
				continue;
			}
			$article_name = $fields[0];
			$seq_no = $fields[1];
			if (empty($article_name)||empty($seq_no)){
				if ($k<(count($lines)-1)){
					echo "error: line $k , article_name seq_no 为空</br>"; 
				}else{
					echo "最后一行 line $k , article_name seq_no 为空</br>"; 
				}
				continue;
			}
			if (($k==0) && ($article_name=="稿件名称")){
				continue;
			}
			
			$chinese_key = $fields[2];
			if (empty($chinese_key)){
				echo "Error: line $k chinese_key is empty</br>";
				continue;
			}
			
			if ($chinese_key<>"无"){
				$temp_r = term::select("select id from jlsterm_78 where chinese_key=\"$chinese_key\"");
				if (!empty($temp_r)){
					echo "$chinese_key is common in 78</br>";
				}
				else
					echo "$chinese_key is not common in 78</br>";
			}
			file_put_contents($outputFileName,sprintf("%d,%s,%s\r\n",$k,$chinese_key,(empty($temp_r)?'N':'Y')),8);
		}
		
	}	
	
	public function addTable(){
		set_time_limit(0);

		//tableName1 += tableName2
		
		////sentence: article_id, seq_no, content, translation, last_modifier, last_modify_time, comment
		//$tableName1 = "sentence"; 
		//$tableName2 = "sentence_78"; 
		//$fields = array(
		//	array('name'=>'article_id','type'=>'','value'=>''),
		//	array('name'=>'seq_no','type'=>'','value'=>''),
		//	array('name'=>'content','type'=>'string','value'=>''),
		//	array('name'=>'translation','type'=>'string','value'=>''),
		//	//array('name'=>'last_modifier','type'=>'string','value'=>''),
		//	//array('name'=>'last_modify_time','type'=>'','value'=>''),
		//	//array('name'=>'comment','type'=>'string','value'=>'')
		//);
		
		//article:id, name, article_type
		$tableName1 = "article"; 
		$tableName2 = "article_78"; 
		$fields = array(
			array('name'=>'id','type'=>'','value'=>''),
			array('name'=>'name','type'=>'string','value'=>''),
			array('name'=>'article_type','type'=>'','value'=>'')
		);
		
		$records = term::select("select * from $tableName2");
		foreach($records as $record){
			$insertClause = $this::buildInsertClause($fields,$record);
			term::select("INSERT INTO $tableName1 $insertClause");
		}
	}
	
	public function buildInsertClause($fields,$record){
		$nameArray = array();
		$valueArray = array();
		foreach($fields as $field){
			$nameArray []= ("`{$field['name']}`");
			if ($field['type']=='string'){
				$valueArray []= ("\"".addslashes($record->{$field['name']})."\"");
			}else{
				$valueArray []= $record->{$field['name']};
			}
		}
		$nameClause = "(".implode(",",$nameArray).")";
		$valueClause = "(".implode(",",$valueArray).")";
		$insertClause = "$nameClause VALUES $valueClause";
		//echo "$nameClause</br>$valueClause</br>$insertClause</br>";
		return $insertClause;
	}
	
	public function singleContained(){
		$singleTerms = array("爱","禅","触","道","定","度","恶","观","行","护","慧","惑","集","见","觉","劫","解","经","净","空","苦","离","理","量","论","慢","灭","明","魔","念","破","取","色","僧","善","舍","识","受","思","伺","我","悟","相","想","邪","心","信","修","寻","业","疑","因","欲","缘","蕴","斋","真","正","证","执","宗");
		foreach($singleTerms as $singleTerm){
			$temp_r=term::select("select * from term where source like \"%liuxl%\" and chinese_key like \"%$singleTerm%\" and chinese_key<>\"$singleTerm\"");
			if (!empty($temp_r)){
				$temp = "";
				$temp_count=count($temp_r);
				foreach($temp_r as $k=>$temp_v){
					if ($k<$temp_count-1)
						$temp .= "{$temp_v->chinese_key}, ";
					else
						$temp .= "{$temp_v->chinese_key}";
				}
			}
			echo "$singleTerm|$temp</br>";
		}
	}
	
	public function fillTermTypeFromTermDetail(){
		set_time_limit(0);
		$terms = term::select("select * from term");
		foreach($terms as $term){
			$termDetails=term_detail::select("select * from term_detail where article_id between 4 and 28 and term_id={$term->id}");
			if (empty($termDetails))
				continue;
			$termType=0;
			foreach($termDetails as $termDetail){
				if ($termDetail->type>=100){
					if (($termType>0) &&($termType!=$termDetail->type)){
						echo "Error:{$term->chinese_key} $termType {$termDetail->type}</br>";
					}
					$termType=$termDetail->type;
				}
			}
			if ($termType>0) {
				echo "Found:{$term->chinese_key} {$termType}</br>";
				term::select("update term SET type=$termType where id={$term->id}");
			}
			else{
				echo "Appread in term_detail:{$term->chinese_key} </br>";
				term::select("update term SET type=1 where id={$term->id}");
			}
		}
	}
	
	public function fillTermFreq(){
		set_time_limit(0);
		$debug1=false;
		$checkFoundInBig=true;
		
		$outputFileName = iconv('UTF-8','GB2312',"F:\\000_booktran\\术语讨论会\\术语频率.txt");		
		file_put_contents($outputFileName,"");
		$terms = term::select("select * from term where (source like \"%jls_78%\" or type=1) and length(chinese_key)>3");
		foreach($terms as $term){
			$needle = $term->chinese_key;
			if ($debug1)	echo "term is {$term->chinese_key}</br>";
			for($book=4;$book<=8;$book++){
				for($class=0;$class<3;$class++) //0:batch 1, 1:batch 2, non-empty, 2: batch 2, empty
					$freq[$book-4][$class]=0;
			}
			
			for($book=4;$book<=8;$book++){
				$sentences=sentence::select("select sentence.id, sentence.content, sentence.batch, sentence.translation from sentence, article where sentence.article_id=article.id and substring(article.name,3,1)=\"$book\" and article_id>=4 and sentence.content like \"%{$term->chinese_key}%\"");
				foreach($sentences as $sentence){
					$hay = $sentence -> content;
					$sentence_id = $sentence -> id;
					if ($sentence->batch==1){
						$class=0;
					}
					else{
						if (strlen($sentence->translation)>1)
							$class=1;
						else
							$class=2;
					}
					
					$curPos=0;
					$cur_occur_no=0;
					$hayLen=mb_strlen($hay);
					do{
						$curFoundPos = mb_strpos($hay,$needle,$curPos);
						if ($curFoundPos===FALSE){
							break;
						}
						
						if ($checkFoundInBig){
							$bigSqls = array();
							$bigSqlChineseKeyClause = "chinese_key like \"%$needle%\" and chinese_key<>\"$needle\"";
							$bigSqls []= "select chinese_key from term, term_detail where term.id=term_detail.term_id and sentence_id=$sentence_id and (term.source like \"%jls_78%\" or term_detail.type=1) and $bigSqlChineseKeyClause";
							$foundInBig = false;
							foreach($bigSqls as $bigSql){
								//if ($debug1)	echo "$bigSql</br>";
								$tempBigs = term::select($bigSql);
								if (empty($tempBigs))
									continue;
								//if ($debug1)	var_dump($tempBigs);
								foreach($tempBigs as $tempBig){
									$bigNeedle = $tempBig->chinese_key;
									//echo "$bigNeedle</br>";
									$smallInBigPos = mb_strpos($bigNeedle,$needle);
									$bigTermPosInSentence = $curFoundPos - $smallInBigPos;
									//echo "$bigTermPosInSentence</br>";
									if ($bigTermPosInSentence<0)
										continue;
									$bigPieceInSentence = mb_substr($hay,$bigTermPosInSentence,mb_strlen($bigNeedle));
									//echo "$bigPieceInSentence</br>";
									if ($bigPieceInSentence==$bigNeedle){
										if ($debug1)	echo "Big found! $needle contained in $bigNeedle, $hay</br>";
										$foundInBig=true;
										break;
									}
								}
								flush();ob_flush();
								if ($foundInBig)
									break;
							}
							if ((!$foundInBig)){
								$freq[$book-4][$class]++;
								//file_put_contents($outputFileName,"$needle|$hay\r\n",8);
							}
						}
	
						$curPos=$curFoundPos+1;
						$cur_occur_no+=1;
					}while($curPos<$hayLen);
				}
				//echo "book$book:$freq</br>";
			} //end of loop book
			if ($debug1)	echo "</br>";
			
			//术语，经常误判，总次数，已完成次数，
			$temp_in_1_5000 = term::select("select * from temp1_5000 where chinese_key=\"$needle\"");
			$in_1_5000 = (!empty($temp_in_1_5000));
			$doneFreq=0;
			$subtotalFreq=0;
			$freqOfBooks="";
			for($book=4;$book<=8;$book++){
				$freqInTheBook=0;
				$freqOfThisBook="";
				for($class=0;$class<3;$class++){ //0:batch 1, 1:batch 2, non-empty, 2: batch 2, empty
					$freqOfThisBook .= ($freq[$book-4][$class].",");
					$freqInTheBook += $freq[$book-4][$class];
					$subtotalFreq += $freq[$book-4][$class];
					if (($book<=6) && $in_1_5000 && $class==0)
						$doneFreq += $freq[$book-4][$class];
				}
				$freqOfThisBook = "$freqInTheBook,$freqOfThisBook";
				$freqOfBooks .= $freqOfThisBook;
			}
			$oftenWrong = (in_array($needle,array("法行","有学","我想","心要","心相","无知","义和","众生心","修善","修得"))) ? "Y" : "N";
			file_put_contents($outputFileName,"$needle,$oftenWrong,$subtotalFreq,$doneFreq,$freqOfBooks\r\n",8);
		}//end of loop term
	}//end of function

	public function listTermContaining(){
		set_time_limit(0);
		$debug1=false;
		$checkFoundInBig=true;
		
		$outputFileName = iconv('UTF-8','GB2312',"F:\\000_booktran\\术语讨论会\\术语-包含关系.txt");		
		file_put_contents($outputFileName,"");
		$terms = term::select("select * from term where (source like \"%jls_78%\" or type=1) and length(chinese_key)>3 and category is null");
		foreach($terms as $term){
			$bigLen = mb_strlen($term->chinese_key)*3;
			file_put_contents($outputFileName,"{$term->chinese_key}|",8);
			$terms2 = term::select("select * from term where (source like \"%jls_78%\" or type=1) and length(chinese_key)>3 and id<>{$term->id} and category is null and length(chinese_key)<$bigLen");
			foreach($terms2 as $term2){
				if (mb_strpos($term->chinese_key,$term2->chinese_key)!==false)
					file_put_contents($outputFileName,"{$term2->chinese_key}|",8);
			}
			file_put_contents($outputFileName,"\r\n",8);
		}	
	}	
	
	public function importSoothHill(){
		$inputFileName = iconv('UTF-8','GB2312',"F:\\000_booktran\\词典工具\\中英佛学词典soothill\\英文团队-佛学词典002-中英佛学辞典-Soothill-2003.txt");
		$inputFile=file_get_contents($inputFileName);
		$startLineNo=83-1;
		$inputLines=explode("\r\n",$inputFile);
		$countLines=count($inputLines);
		for($k=$startLineNo;$k<=$countLines-6;$k++){
			$curLine = $inputLines[$k];
			if (empty($curLine))
				continue;
			$firstWSPos = mb_strpos($curLine," ");
			if ($firstWSPos!==false){
				$chinese_key = mb_substr($curLine,0,$firstWSPos);
				$detail = mb_substr($curLine,$firstWSPos+1);
				//echo "$chinese_key***$detail</br>";
			}else{
				$chinese_key = $curLine;
				$detail = "";
				//echo "$curLine</br>";
			}
			$sql="INSERT INTO sooth_hill (chinese_key,detail) VALUES (".
				"\"".addslashes($chinese_key)."\",".
				"\"".addslashes($detail)."\")";
			term::select($sql);
		}
	}
	
	public function importFromStardict(){
		$dics = term::select("select * from fodict_dir");
		$terms = term::select("select * from term order by batch desc");
		term::select("DELETE from term_info where 1");
		foreach($dics as $dic){
			foreach($terms as $term){
				$chinese_key = $term->chinese_key;
				//$chinese_key = $this::convRareChar($chinese_key);
				$idStr = sprintf("%08u",$term->id);
				//$chinese_key_conv = iconv('UTF-8','GB2312',$chinese_key);
				//$stardict_name_conv = iconv('UTF-8','GB2312',$dic->stardict_name);
				//$inputFileName = "F:\\000_fo_译网big\\stardict\\$chinese_key_conv@$stardict_name_conv.txt";
				$inputFileName = iconv('UTF-8','GB2312',"F:\\000_fo_译网big\\stardict\\v2.0\\$idStr@{$dic->stardict_name}.txt");
				if (file_exists($inputFileName)){
					echo "$chinese_key@{$dic->stardict_name} exist</br>";
					$info = file_get_contents($inputFileName);
					term::select("INSERT into term_info (term_id,`source`,`info`) VALUES(
						{$term->id},
						\"{$dic->display_name}\",
						\"".addslashes($info)."\"
						)");
				}else{
					echo "$chinese_key@{$dic->stardict_name} NOT exist</br>";
				}
			}
		}
	}
	
	public function convRareChar($str1){
		$str2=$str1;
		
		$rares = array("吽","口牛","阇","门者","槃","般木","洴","水并","啰","口罗","祇","示氏","呾","口旦","呰","此口","顗","岂类","无明闇","门音","憍","心乔","鬘","蔓","睺","目候","菴","草奄","","","","","","","","","","","","","","","","","","");
		$rareCount = count($rares)/2;
		for($i=0;$i<$rareCount;$i++){
			$str2 = str_replace($rares[2*$i],$rares[2*$i+1],$str2);
		}
		//深行阿阇梨 涅槃饭 洴舍 泥啰耶 波罗提木叉僧祇戒本 波利呾罗拘迦 毁呰 智顗 无明闇 憍陈如 华鬘 罗睺罗 菴摩罗果
		 
		
		switch($str1){
			case "玉呬耶经":
				$str2="玉口四耶经";
				break;
				
			case "猕猴著黐":
				$str2="猕猴著黍离";
				break;
				
			case "特欹拏伽陀":
				$str2="特欹奴手伽陀";//$str2="特奇欠拏伽陀";
				break;
				
			case "牪货":
				$str2="牛牛货";
				break;
				
			case "牛头栴檀":
				$str2="牛头木舟檀";
				break;
				
			case "爱罥":
				$str2="爱四口月";
				break;
				
			case "煆发":
				$str2="火假发";
				break;
				
			case "烹佛煆祖":
				$str2="烹佛火假祖";
				break;
				
			case "烝砂作饭":
				$str2="蒸砂作饭";
				break;

			case "烹佛煆祖":
				$str2="烹佛火假祖";
				break;
				
			case "烹佛煆祖":
				$str2="烹佛火假祖";
				break;
				
			case "烹佛煆祖":
				$str2="烹佛火假祖";
				break;
				
			case "烁羯啰":
				$str2="烁羯口罗";
				break;
				
			case "濊佉阿悉底迦":
				$str2="水岁人去阿悉底迦";
				break;
				
			case "满拏":
				$str2="满奴手";
				break;
				
			case "清净涅槃":
				$str2="清净涅般木";
				break;
				
			case "烁羯啰":
				$str2="烁羯口罗";
				break;
				
			case "烁羯啰":
				$str2="烁羯口罗";
				break;
				
			case "烁羯啰":
				$str2="烁羯口罗";
				break;
				
			case "烁羯啰":
				$str2="烁羯口罗";
				break;
				
		}
		return $str2;
	}
	
	public function exportTerm(){
		set_time_limit(0);
		$terms = term::select("select * from term order by batch desc");
		$outputFileName = iconv('UTF-8','GB2312',"E:\\wamp\\www\\tonggao\\sdcv\\term_batch.txt");
		file_put_contents($outputFileName,"");
		foreach($terms as $term){
			file_put_contents($outputFileName,sprintf("%1d|%08u|%s\n",$term->batch,$term->id,$term->chinese_key),8);
		}
	}
	
	public function importTermFreq(){
		set_time_limit(0);
		//$terms = term::select("select * from term order by batch desc");
		$inputFileName = iconv('UTF-8','GB2312',"F:\\000_booktran\\术语讨论会\\术语频率-20180407.xlsx");
		Excel::load($inputFileName, function($reader) {
			$sheetIdx = 3-1;
			$headerRowIdx = 2 - 1;	
			
			$reader = $reader->getSheet($sheetIdx);
			$results = $reader->toArray();
			
			foreach($results as $row_i=>$row){
				if ($row_i<$headerRowIdx)
					continue;
				if ($row_i==$headerRowIdx){
					foreach($row as $col_i=>$cell){
						$cell=trim($cell);
						if ($cell=="术语")
							$chinese_key_col_idx = $col_i;
						if ($cell=="总频率")
							$freqColIdx = $col_i;
					}
					continue;
				}
				
				$terms = term::select("select id from term where chinese_key=\"{$row[$chinese_key_col_idx]}\"");
				if (!empty($terms)){
					term::select("update term SET `freq_in_4-8`={$row[$freqColIdx]} where id={$terms[0]->id}");
					echo "术语:{$row[$chinese_key_col_idx]}  频率:{$row[$freqColIdx]} updated</br>";
				}else{
					echo "术语:{$row[$chinese_key_col_idx]} NOT FOUND</br>";
				}
				
			}
	    });		
	}
	
	public function combineDupDDB(){
		$tableName = "term_raw_ddb";
		$dupTerms = term::select("select * from (select chinese_key, count(*) mycount from $tableName group by chinese_key) as c where mycount>1");
		//var_dump($dupTerms);
		foreach($dupTerms as $dupTerm){
			$curChineseKey = $dupTerm->chinese_key;
			$termsOfCurChineseKey = term::select("select id, english from $tableName where chinese_key=\"$curChineseKey\"");
			$firstId = $termsOfCurChineseKey[0]->id;
			$combinedEnglish = $termsOfCurChineseKey[0]->english;
			foreach($termsOfCurChineseKey as $k=>$termOfCurChineseKey){
				if ($k==0)
					continue;
				$combinedEnglish .= ("[b]".$termOfCurChineseKey->english);
			}
			echo "$curChineseKey $combinedEnglish</br>";
			term::select("update $tableName SET english=\"".addslashes($combinedEnglish)."\" where id=$firstId");
			foreach($termsOfCurChineseKey as $k=>$termOfCurChineseKey){
				if ($k==0)
					continue;
				term::select("delete from $tableName where id={$termOfCurChineseKey->id}");
			}
		}
	}
	
	public function importTermInfoRaw(){
		//creatDB
		//readfile
		
		//中文处理
		//如何插入term表
		//$source = "Princeton2";	$inputFileName = "术语库-词典-普林斯顿.csv"; //中文处理NOT DONE YET
		$source = "FoGuang2";	$inputFileName = "非PDF格式\\非PDF格式\\佛光大辞典.xlsx";
		
		$replaceArray = array(
			array('(','['),
			array(')','['),
		);
		
		//createDB
		$tableName = "term_raw_$source";
		term::select("DROP TABLE IF EXISTS `$tableName`");
		term::select("CREATE TABLE `$tableName` (
			`id` int(10) UNSIGNED NOT NULL,
			`term_id` int(8) UNSIGNED NOT NULL DEFAULT '0',
			`chinese_key` varchar(60) NOT NULL,
			`info` text NOT NULL,
			`source` varchar(12) NOT NULL DEFAULT \"$source\"
		) ENGINE=MYISAM DEFAULT CHARSET=utf8");
		term::select("ALTER TABLE `$tableName`  ADD PRIMARY KEY (`id`)");
		term::select("ALTER TABLE `$tableName`  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT");
  
		//readfile
		$realPath = iconv('UTF-8','GB2312',"F:\\000_booktran\\词典源\\fromHaiRi\\$inputFileName");		
		$cellData = [];
		$chineseKeyColNo = 1;
		$englishColNo = 2;
		Excel::load($realPath, function($reader) use (&$cellData) {
			$reader = $reader->getSheet(0);
			$cellData = $reader->toArray();
	    });
		//var_dump($cellData);

		//中文处理
		//$puncArray = array(
		//	array(
		//		'leftDelimeterArray' => array
		//);
		$utfChn = "[\x{4e00}-\x{9fa5}]";
		$utfChns = $utfChn."{0,}";
		foreach($cellData as $rowIdx=>$rowData){
			$chineseKey=$rowData[$chineseKeyColNo-1];
			$info = $rowData[$englishColNo-1];
			if (empty($chineseKey))
				continue;
			/*
			$chineseKeyArray=array($chineseKey);
			
			$str = "开头[]结束";
			$rule = "/($utfChns)\[($utfChns)\]($utfChns)/us";
			preg_match($rule, $str,$m);
			var_dump($m);
			*/
			
			/*
			$leftPos = mb_strpos($chineseKey, "[");
			if ($leftPos!==true){
				$rightPos = mb_strpos($chineseKey, "]");
				if ($rightPos===true){
					echo "Error $chineseKey [ w/o ]</br>";
					continue;
				}
				else{
					$leftStr = 
					$middleStr = mb_substr($chineseKey,$leftPos, $rightPos-$leftPos+1);
					echo "$chineseKey $middleStr</br>";
					
					continue;
				}
			}
			*/
		}
		
		//插入数据库
		foreach($cellData as $rowIdx=>$rowData){
			$chineseKey=$rowData[$chineseKeyColNo-1];
			$info = $rowData[$englishColNo-1];
			$termId = 0;
			$termIds = term::select("select id from term where chinese_key=\"$chineseKey\"");
			if (!empty($termIds)){
				$termId = $termIds[0]->id;
			}
			
			term::select(
				"INSERT INTO $tableName  (term_id,chinese_key,info)VALUES(".
				"$termId,".
				"\"".addslashes($chineseKey)."\",".
				"\"".addslashes($info)."\"".
				")"
				);
		}
		
	}
	
	public function extractSanskritPali(){
		set_time_limit(0);
		
		$source = "FoGuang2";	
		$tableName = "term_raw_$source";
//梵名 Ikṣvāku，巴利名 Okkāka。
//梵名 Vindhya, Vindhyāṭavi, 巴利名 Viñjhā, Viñjhāṭavi。
//梵名 Uttara-kuru，巴利语同。
//梵语 ṡikṣā-karaṇīya，巴利语 sikkhā-karaṇīya。	
//梵语 ṡreṣṭhin（音译室隶瑟㫞），或 gṛha-pati（音译疑叻贺况底）；巴利语 seṭṭhin，或 gaha-pati。
//梵语 stūpa 之音译，又作卒都婆、窣堵波、薮斗婆。巴利语为 thūpa。
//梵语 kavaḍiṃ-kārāhāra 或 kavalī-kārāhāra, 巴利语 kabaliṅkārāhāra。
//梵语 samāhita，巴利语同。
//梵语 jīvajīva, jīvajīvaka, jīvaṃjīva, jīvaṃjīvaka。巴利语同。
//梵语 tamālapatra，音译多摩罗跋；巴利语 tamāla，
//梵语 grāma，音译枷喇摩；巴利语 gāma。??
//梵话 anuttara，巴利语同。
//梵 Cūḍapanthaka, Cullapatka, Kṣullapanthaka, Śuddhipaṃthaka，巴利名 Cullapanthaka, Cūḷapanthaka。

//梵语有 vyasta, ekaika, pratikṣaṇam 等字。
//梵语 sakṛdāgāmi-pratipannaka。		
//梵语 kṣetra，田土、国土之意。
//梵名 Anantamukhasādhakadhāraṇī。

//TODO
		$utfChn = "[\x{4e00}-\x{9fa5}]";
		$utfChns = $utfChn."{0,}";

		$ruleBothArray=array(
			"/.*梵名 (.*)，巴利名 (.*?)[。|，]/us", //共有266个，需要除去11个请参阅，
			"/.*梵名 (.*), 巴利名 (.*)。*/us",
			"/.*梵名 (.*)，巴利名同。*/us",
			"/.*梵名 (.*)，巴利语同。*/us",
			"/.*梵语 (.*)，巴利语 (.*)。*/us",
			"/.*梵语 (.*)；巴利语 (.*)。*/us",
			"/.*梵语 (.*)。巴利语 (.*)。*/us",
			"/.*梵语 (.*), 巴利语 (.*)。*/us",
			"/.*梵语 (.*)，巴利语 (.*)。*/us",
			"/.*梵语 (.*)，巴利语 (.*)，*/us",
			"/.*梵语 (.*)，巴利语同。*/us",
			"/.*梵语 (.*)。巴利语同。*/us",
			"/.*梵语 (.*)($utfChns)。巴利语同。*/us",
			"/.*梵语 (.*)($utfChns)。巴利语为(.*)。*/us",//梵语 stūpa 之音译，又作卒都婆、窣堵波、薮斗婆。巴利语为 thūpa。
			"/.*梵语 (.*)，巴利名(.*)。*/us",
			"/.*梵话 (.*)，巴利语同。*/us",
			"/.*梵 (.*)，巴利名(.*)。*/us",
		);
		
//梵语有 vyasta, ekaika, pratikṣaṇam 等字。
//梵语 sakṛdāgāmi-pratipannaka。		
//梵语 kṣetra，田土、国土之意。
//梵名 Anantamukhasādhakadhāraṇī。
//梵 para-tantra-svabhāva。梵 Catuḥsatya-nirdeṡa。

		$ruleSanskritArray=array(
			"/梵语有 (.*)等字。.*/us",
			"/梵语(.*)。.*/us",
			"/梵名(.*)。.*/us",
			"/^梵(.*)。/us",				
		);
		
		$rulePaliArray=array(
			"/巴利语(.*)。.*/us",
			"/巴利名(.*)。.*/us",
		);
		$terms = term::select("select * from $tableName");
		$leftDecoration = "<font color=\"red\">";
		$rightDecoration = "</font>";
		
		//$info = "请参阅华严经";
		//$info2 = $this::removePlsRefer($info);
		//exit;
		foreach($terms as $term){
			$info = $term->info;
//有两个error
//Error #　【探玄记→】请参阅华严经探玄记 或 华严经疏\n 　临济宗七事随身之一、四喝之一。略称探草。因其作用与目的，有不同解释：(一)指探竿、影草皆为渔者之工具。探竿，系束鹈羽于竿头，探于水中，诱聚群鱼于一处，然后以网漉之。影草，系刈草浸水中，则群鱼潜影，然后以网漉之。此皆渔者聚鱼之方便法。(二)指探竿、影草为盗者之道具。探竿，指盗者将之从窗、壁等洞穴插入，以探测室内动静之竹竿。影草，为可隐身之蓑衣，穿着之便于窃盗。又探竿影草，于禅家，引申为师家探测学人，以试其器量。〔临济录勘辨、从容录第八十一则〕（参阅「济宗七事随身」6445、「临济四喝」6505）#p4581\n contains 请参阅
//Error #请参阅大乘庄严经论 或 大庄严经论 经 （Hodgson, Brian Houghton; 1800～1894）英国之东方学学者、外交官。十八岁即至印度，任东印度公司职员、外交官；任职于波斯、尼泊尔时（1833～1848），对印度殖民地之经营，献策良多。一八四八年退职后，栖隐于喜马拉雅山山麓，专心研究动物学、地理学、人类学、语言学。搜集一万种以上之鸟类，并介绍尼泊尔、西藏之风俗、文物。致力于三百八十一部梵语大乘教典之写本类之搜集、翻译、刊行，并将梵语佛教教典介绍至欧洲，引起甚大之反应。其中，金刚针论（梵 Vajrasūcī）乃梵语佛典之最早英译本（1828）。其著作有：Illustrations of the Literature and Religion of the Buddhists, 1841；Essays on the Language, Literature and Religion of Nepal and Tibet, 1874；Aborigines of India; Sketch of Buddhism, derived from Buddha Scriptures of the Nepal；Miscellaneous Essays relating to Indian Subjects, 2 vols., 1800 等。被视为真正研究佛教之拓荒者。#p4778\n contains 请参阅
			//if (mb_strpos($info,"临济宗七事随身之一、四喝之一。略称探草")===false)
			//	continue;
			$this::removePlsRefer($info);
			continue;
			
			$sanskritStartPos = mb_strpos($info,"梵");
			$paliStartPos = mb_strpos($info,"巴利");
			
			if (($sanskritStartPos!==false)&&($paliStartPos!==false)){
				$found = false;
				foreach($ruleBothArray as $i=>$ruleBoth){
					preg_match($ruleBoth, $info,$m);
					if (!empty($m)){
						$found=true;
						switch($ruleBoth){
							case "/.*梵名 (.*)，巴利名 (.*?)[。|，]/us": //需要除去11个请参阅，需要细化"
								//$sanskritNameStartPos	=0:152, =3~12:102, =19,101,380,571:1, total:258
								$sanskritNameStartPos=mb_strpos($info,"梵名 ");
								$sanskritNameEndPos = $sanskritNameStartPos + mb_strlen("梵名 ") - 1;
								$paliNameStartPos=mb_strpos($info,"，巴利名 ",$sanskritNameEndPos+1);
								$paliNameEndPos = $paliNameStartPos + mb_strlen("，巴利名 ") - 1;
								$puncturePos1 = mb_strpos($info,"。",$paliNameEndPos);
								$puncturePos2 = mb_strpos($info,"，",$paliNameEndPos);
								if ($puncturePos1==false)
									$puncturePos = $puncturePos2;
								else if ($puncturePos2==false)
									$puncturePos = $puncturePos1;
								else
									$puncturePos = min($puncturePos1,$puncturePos2);
								$plsReferStartPos = mb_strpos($info,"请参阅");
								if (($plsReferStartPos!==false)){ 
								//TODO, recoded
									echo "$info</br>";
									$found=false;
								}else{
									$decStartLen=array();
									$decStartLen[0]=array('start'=>$sanskritNameEndPos+1,'len'=>$paliNameStartPos-$sanskritNameEndPos-1);
									$decStartLen[1]=array('start'=>$paliNameEndPos+1,'len'=>$puncturePos-$paliNameEndPos-1);
									$sanskrit = mb_substr($info,$decStartLen[0]['start'],$decStartLen[0]['len']);
									$pali = mb_substr($info,$decStartLen[1]['start'],$decStartLen[1]['len']);
									$decInfo = addDecoration2($info,$decStartLen,$leftDecoration,$rightDecoration);
									if ($sanskritNameStartPos>18){
									;//	echo "$decInfo</br>";
									//	echo "$sanskrit $pali</br>";
									}
									//echo "$sanskritNameStartPos</br>";
								}
								break;
							
						}
					}
				}
				if ($found==false){
					;//echo "$info</br>";
				}
/*				$sanskritEndPos = mb_strpos($info,"巴利");
				if ($sanskritEndPos!==false){
					//$sanskrit = mb_substr($info,$sanskritStartPos, $sanskritEndPos-$sanskritStartPos+1);
					//echo "梵文 is $sanskrit in $info </br>";
				}else{
					echo "梵 found but 巴利 not found in $info</br>";
				}
*/				
			}elseif($sanskritStartPos!==false){
				$found = false;
				foreach($ruleSanskritArray as $ruleSanskrit){
					preg_match($ruleSanskrit,$info,$m);
					//var_dump($m);
					if (!empty($m)){
						$found=true;
						break;
					}
				}
				if ($found==false){
					;//echo "$info</br>";
				}
			}elseif($paliStartPos!==false){
				$found = false;
				foreach($rulePaliArray as $rulePali){
					preg_match($rulePali,$info,$m);
					//var_dump($m);
					if (!empty($m)){
						$found=true;
						break;
					}
				}
				if ($found==false){
					//echo "$info</br>";
				}
			}
		}
	}
	
	public function removePlsRefer($info){
		set_time_limit(0);
		$debug1=false;
		$debug2=false;
		if ($debug2)	echo "$info</br>";
		$oldLen  = $originalLen = mb_strlen($info);
		$maxReduction = 0;
		while(1){
			$info2=$this::removePlsReferCore($info);
			$newLen = mb_strlen($info2);
			if ($debug2)	{echo "oldLen=$oldLen newLen=$newLen diff="; echo $oldLen-$newLen."$info2 </br>";}
			if ($oldLen==$newLen){
				if ($originalLen>$newLen){
					if ($debug2)	echo "originalLen=$originalLen newLen=$newLen diff="; echo $reduction."</br>";	
				}		
				if (mb_strpos($info2,"请参阅")!==false)
					echo "Error $info contains 请参阅</br>";
				
				return $info2;
			}
			$reduction = $oldLen-$newLen;
			if ($debug1)
				echo "$reduction</br>";
			
			$oldLen = $newLen;
			$info=$info2;
		}
	}
	
//此函数不完善，部分例外如下。但不影响梵文和巴利文的提取
//法界观门 或华严法界观门 据北本大涅盘经卷二十一载，涅盘，大灭度之义
//摩诃僧祇律为十二头陀行之一
//出三藏记集（543～631）隋唐时代僧
//优婆夷净行法门经全一卷
//景德传灯录 或 灯录势至，
//唐大和上东征传 后面的
//成唯识论述记 凡十卷
//观无量寿经义疏 嘉，欢喜之义；会，来集之义
//贞元新定释教目录（1878～1953）我国近代僧
//圆觉经 指圆觉经所说之三种观法
// 一山国师妙慈弘济大师语录（1062～1162）
//妙好宝车经为一种由唐代「变文」直接衍生之讲唱文学
//笑隐大欣禅师语录（1892～1986）福建惠安人
//永源寂室和尚语录寂留明

	public function removePlsReferCore($info){
		$debug1=false;
		//special case: "请参阅"后面没有空格
		$specials = array("^请参阅解深密经 ","^请参阅明觉禅师语录    #");
		$plsReferStr = "请参阅";
		$str2Array = array($plsReferStr,"#","。","\\n","<一>","梵语","梵名");
		$str2bArray = array(" ","经","广录","感通录","记集","颂","论","语录","释教录","教仪");
		
		//process special cases
		foreach($specials as $special){
			$info2 = preg_replace("/$special/","",$info);
			if (mb_strlen($info2)< mb_strlen($info)){
				if ($debug1)	echo "$special HAS BEEN REMOVED</br>";
				return $info2;
			}
		}
				
		$plsReferStartPos = mb_strpos($info,$plsReferStr);
		if ($plsReferStartPos===false){
			//if ($debug1)	echo "NOTHING HAS BEEN REMOVED</br>";
			return $info;
		}
		
		$plsReferEndPos = $plsReferStartPos + mb_strlen($plsReferStr)-1;
		while($plsReferEndPos<mb_strlen($info)-1){
			if (mb_substr($info,$plsReferEndPos+1,1)==" ")
				$plsReferEndPos++;
			else
				break;
		}
		

		$pos2 = mb_min_strpos($info,$str2Array,$plsReferEndPos+1);
		
		if ($pos2!==false){
			//如果$middleStr包含"或"，间隔可以加大
			$orPos = mb_strpos($info,"或",$plsReferEndPos+1);
			$distance = 32;
			if (($orPos!==false) && ($orPos<$pos2))
				$distance += 16;
			if ($pos2-$plsReferStartPos<=$distance){
				;
			}
			else{
				$pos2 = mb_min_strpos($info,$str2bArray,$plsReferEndPos+1);
				/* just for debug
				if ($pos2===false){
					echo "$pos2".$pos2-$plsReferStartPos.mb_substr($info,0,40)."</br>";
				}else{
					;
				}
				*/
			}
		}
		
		$middleStr = mb_substr($info,$plsReferEndPos+1,$pos2-$plsReferEndPos-1);
		//TODO if (mb_strlen($middl)>40, print $middleStr+-
		if (preg_match("/梵文|梵语|梵名|巴利/",$middleStr)>0){
			echo "Error $middleStr contains 梵文|梵语|梵名|巴利</br>";
		}
		if ($debug1){
			//echo "before:  ".$info."</br>";
			echo "removed: ".$middleStr."</br>";
			//echo "after:   ".mb_rmv_substr($info,$plsReferStartPos,$pos2-$plsReferStartPos)."</br>";
		}
		return mb_rmv_substr($info,$plsReferStartPos,$pos2-$plsReferStartPos);
	}

	public function importTermRefInfo(){
		set_time_limit(0);
		$dryRun=false;
		//$inputFileName = ($bookNo==7)?"第7册引文-20180320.xlsx":"第八册引文-20180320.xlsx";
		$inputRealPath = iconv('UTF-8','GB2312',"F:\\000_booktran\\数据准备\\精进工作坊\\01\\精进工作坊-round01.xlsx");	
		$tableName = "jjgzf_01";
		$sheetIndex = 0;
		$excelHasHeader = 1;
		$ignoreEmptyField=true;
		
		$cellData = [];
		Excel::load($inputRealPath, function($reader) use (&$cellData,$sheetIndex) {
			$reader = $reader->getSheet($sheetIndex);
			$cellData = $reader->toArray();
		});
		
		$colCnt = count($cellData,2);
		$fieldNameArray = array("d_seq_no","d_chinese_key","d_freq_grade","d_proposal","d_adopted","d_comment","d_lamrim","d_dic_english","d_ddb","d_current_article","d_sanskrit","d_pali","d_foguang2","d_dic_chinese","d_article_456");
		$chineseKeyColIndex = 1;

		foreach($cellData as $rowIndex=>$rowData){
			if (($excelHasHeader) && ($rowIndex==0))
				continue;
			$curChineseKey = trim($rowData[$chineseKeyColIndex]);
			$termR = term::select("select id from term where chinese_key=\"$curChineseKey\"");
			echo "select id from term where chinese_key=\"$curChineseKey\"</br>";
			if (empty($termR)){
				echo "term $curChineseKey not exist</br>";
			}else{
				$curTermId = $termR[0]->id;
				$updateSql = "UPDATE TERM SET ";
				foreach($fieldNameArray as $fieldIndex=>$fieldName){
					if ($fieldName=="d_chinese_key")
						continue;
					$curValue = $rowData[$fieldIndex];
					if ($ignoreEmptyField && empty($curValue))
						continue;
					$updateSql .= " `$fieldName`=\"".addslashes($curValue)."\" ,";
				}
				$updateSql .= " d_flag=1 where id=$curTermId";
				if ($dryRun)
					echo "sql is $updateSql</br>";
				else{
					term::select($updateSql);
					echo "$curChineseKey updated</br>";
				}
			}
		}
	}

	
} //!- PrepareController
?>
